{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f74dfa5c-a599-4691-ab8a-281d21c96573",
   "metadata": {},
   "source": [
    "## Preprocessing of Loan Default Dataset\n",
    "\n",
    "### Introduction\n",
    "\n",
    "We use the dataset of [Tianchi Competetion](https://tianchi.aliyun.com/competition/entrance/531830/information) to train our loan default rate estimation. In this notebook, we preprocess the dataset and generate features, which refers to some execellent work listed as below:\n",
    "\n",
    "* **Overview**: https://tianchi.aliyun.com/notebook-ai/detail?spm=5176.12586969.1002.6.3b30250fXUZ5fy&postId=129318\n",
    "* **EDA**: https://tianchi.aliyun.com/notebook-ai/detail?spm=5176.12586969.1002.12.3b30250fXUZ5fy&postId=129320\n",
    "* **Feature Eningeering**: https://tianchi.aliyun.com/notebook-ai/detail?spm=5176.12586969.1002.6.3b30b135z4zdwX&postId=129321"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "84910c14-6b02-4cc5-8e68-731e6abccbad",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import warnings\n",
    "from tqdm import tqdm\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a44b36bb-0618-4e58-9fab-e0065a90c017",
   "metadata": {},
   "source": [
    "### Read data\n",
    "\n",
    "To download the dataset to your own s3 bucket:\n",
    "\n",
    "* Fill {YOUR_S3_BUCKET} and {YOUR_S3_PATH} with your preferred values in the following cell.\n",
    "* Uncomment the cell by removing the leading # character.\n",
    "* Execute the cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3068c5af-7331-4025-aacb-306e57bb0b9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !aws s3 cp ${MY_S3_BUCKET}/risk/tianchi/train.csv .\n",
    "# !aws s3 cp ${MY_S3_BUCKET}/risk/tianchi/testA.csv ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6abadb98-e35d-47de-832d-dcb1254bddfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_train = pd.read_csv('./train.csv')\n",
    "data_test_a = pd.read_csv('./testA.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "47a9809b-a07a-49d8-b1a7-d081499f5554",
   "metadata": {},
   "outputs": [],
   "source": [
    "# numerical features and categorical features\n",
    "numerical_fea = list(data_train.select_dtypes(exclude=['object']).columns)\n",
    "category_fea = list(filter(lambda x: x not in numerical_fea, list(data_train.columns)))\n",
    "label = 'isDefault'\n",
    "numerical_fea.remove(label)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b5a9f8f6-b828-4f68-983a-56bed31ddff0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'loanAmnt',\n",
       " 'term',\n",
       " 'interestRate',\n",
       " 'installment',\n",
       " 'employmentTitle',\n",
       " 'homeOwnership',\n",
       " 'annualIncome',\n",
       " 'verificationStatus',\n",
       " 'purpose',\n",
       " 'postCode',\n",
       " 'regionCode',\n",
       " 'dti',\n",
       " 'delinquency_2years',\n",
       " 'ficoRangeLow',\n",
       " 'ficoRangeHigh',\n",
       " 'openAcc',\n",
       " 'pubRec',\n",
       " 'pubRecBankruptcies',\n",
       " 'revolBal',\n",
       " 'revolUtil',\n",
       " 'totalAcc',\n",
       " 'initialListStatus',\n",
       " 'applicationType',\n",
       " 'title',\n",
       " 'policyCode',\n",
       " 'n0',\n",
       " 'n1',\n",
       " 'n2',\n",
       " 'n3',\n",
       " 'n4',\n",
       " 'n5',\n",
       " 'n6',\n",
       " 'n7',\n",
       " 'n8',\n",
       " 'n9',\n",
       " 'n10',\n",
       " 'n11',\n",
       " 'n12',\n",
       " 'n13',\n",
       " 'n14']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numerical_fea"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6d3eda00-8e86-43c0-ac9f-32283f798331",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['grade', 'subGrade', 'employmentLength', 'issueDate', 'earliesCreditLine']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "category_fea"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d77b9099-67bc-4997-ba57-d86c548559e4",
   "metadata": {},
   "source": [
    "### Fill null values for numerical and categorical features seperately"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "96667cdd-e6fb-428f-81cf-26bf8cdf5a03",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                        0\n",
       "loanAmnt                  0\n",
       "term                      0\n",
       "interestRate              0\n",
       "installment               0\n",
       "employmentTitle           1\n",
       "homeOwnership             0\n",
       "annualIncome              0\n",
       "verificationStatus        0\n",
       "purpose                   0\n",
       "postCode                  1\n",
       "regionCode                0\n",
       "dti                     239\n",
       "delinquency_2years        0\n",
       "ficoRangeLow              0\n",
       "ficoRangeHigh             0\n",
       "openAcc                   0\n",
       "pubRec                    0\n",
       "pubRecBankruptcies      405\n",
       "revolBal                  0\n",
       "revolUtil               531\n",
       "totalAcc                  0\n",
       "initialListStatus         0\n",
       "applicationType           0\n",
       "title                     1\n",
       "policyCode                0\n",
       "n0                    40270\n",
       "n1                    40270\n",
       "n2                    40270\n",
       "n3                    40270\n",
       "n4                    33239\n",
       "n5                    40270\n",
       "n6                    40270\n",
       "n7                    40270\n",
       "n8                    40271\n",
       "n9                    40270\n",
       "n10                   33239\n",
       "n11                   69752\n",
       "n12                   40270\n",
       "n13                   40270\n",
       "n14                   40270\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train[numerical_fea].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "02769fbb-355e-4304-91e1-2740d0d57894",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "grade                    0\n",
       "subGrade                 0\n",
       "employmentLength     46799\n",
       "issueDate                0\n",
       "earliesCreditLine        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train[category_fea].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "157512e0-980f-48a1-aca3-2479f8a80594",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for numerical features we use median values\n",
    "data_train[numerical_fea] = data_train[numerical_fea].fillna(data_train[numerical_fea].median())\n",
    "data_test_a[numerical_fea] = data_test_a[numerical_fea].fillna(data_train[numerical_fea].median())\n",
    "\n",
    "# for categorical features we use mode values\n",
    "data_train[category_fea] = data_train[category_fea].fillna(data_train[category_fea].mode())\n",
    "data_test_a[category_fea] = data_test_a[category_fea].fillna(data_train[category_fea].mode())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "36613007-8808-468d-aaf4-c37849d474b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                        0\n",
       "loanAmnt                  0\n",
       "term                      0\n",
       "interestRate              0\n",
       "installment               0\n",
       "grade                     0\n",
       "subGrade                  0\n",
       "employmentTitle           0\n",
       "employmentLength      46799\n",
       "homeOwnership             0\n",
       "annualIncome              0\n",
       "verificationStatus        0\n",
       "issueDate                 0\n",
       "isDefault                 0\n",
       "purpose                   0\n",
       "postCode                  0\n",
       "regionCode                0\n",
       "dti                       0\n",
       "delinquency_2years        0\n",
       "ficoRangeLow              0\n",
       "ficoRangeHigh             0\n",
       "openAcc                   0\n",
       "pubRec                    0\n",
       "pubRecBankruptcies        0\n",
       "revolBal                  0\n",
       "revolUtil                 0\n",
       "totalAcc                  0\n",
       "initialListStatus         0\n",
       "applicationType           0\n",
       "earliesCreditLine         0\n",
       "title                     0\n",
       "policyCode                0\n",
       "n0                        0\n",
       "n1                        0\n",
       "n2                        0\n",
       "n3                        0\n",
       "n4                        0\n",
       "n5                        0\n",
       "n6                        0\n",
       "n7                        0\n",
       "n8                        0\n",
       "n9                        0\n",
       "n10                       0\n",
       "n11                       0\n",
       "n12                       0\n",
       "n13                       0\n",
       "n14                       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d24dd682-c3d9-4257-a0ef-f440cbaea5ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ffill for null values `employmentLength`\n",
    "data_train = data_train.fillna(axis=0, method='ffill')\n",
    "data_test_a = data_test_a.fillna(axis=0, method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6f93c797-2f26-4d33-9ee6-6c691bbb25ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                    0\n",
       "loanAmnt              0\n",
       "term                  0\n",
       "interestRate          0\n",
       "installment           0\n",
       "grade                 0\n",
       "subGrade              0\n",
       "employmentTitle       0\n",
       "employmentLength      0\n",
       "homeOwnership         0\n",
       "annualIncome          0\n",
       "verificationStatus    0\n",
       "issueDate             0\n",
       "isDefault             0\n",
       "purpose               0\n",
       "postCode              0\n",
       "regionCode            0\n",
       "dti                   0\n",
       "delinquency_2years    0\n",
       "ficoRangeLow          0\n",
       "ficoRangeHigh         0\n",
       "openAcc               0\n",
       "pubRec                0\n",
       "pubRecBankruptcies    0\n",
       "revolBal              0\n",
       "revolUtil             0\n",
       "totalAcc              0\n",
       "initialListStatus     0\n",
       "applicationType       0\n",
       "earliesCreditLine     0\n",
       "title                 0\n",
       "policyCode            0\n",
       "n0                    0\n",
       "n1                    0\n",
       "n2                    0\n",
       "n3                    0\n",
       "n4                    0\n",
       "n5                    0\n",
       "n6                    0\n",
       "n7                    0\n",
       "n8                    0\n",
       "n9                    0\n",
       "n10                   0\n",
       "n11                   0\n",
       "n12                   0\n",
       "n13                   0\n",
       "n14                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ce73629-efb8-4774-a0d3-e5b5456f5c93",
   "metadata": {},
   "source": [
    "### Transform `issueDate` into numerical values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "9414d597-28f2-467c-9a03-6500e0fda09a",
   "metadata": {},
   "outputs": [],
   "source": [
    "for data in [data_train, data_test_a]:\n",
    "    data['issueDate'] = pd.to_datetime(data['issueDate'],format='%Y-%m-%d')\n",
    "    startdate = datetime.datetime.strptime('2007-06-01', '%Y-%m-%d')\n",
    "    data['issueDateDT'] = data['issueDate'].apply(lambda x: x-startdate).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "10c20c26-bc81-476b-aef9-54d5ad436285",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>loanAmnt</th>\n",
       "      <th>term</th>\n",
       "      <th>interestRate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>subGrade</th>\n",
       "      <th>employmentTitle</th>\n",
       "      <th>employmentLength</th>\n",
       "      <th>homeOwnership</th>\n",
       "      <th>annualIncome</th>\n",
       "      <th>verificationStatus</th>\n",
       "      <th>issueDate</th>\n",
       "      <th>purpose</th>\n",
       "      <th>postCode</th>\n",
       "      <th>regionCode</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinquency_2years</th>\n",
       "      <th>ficoRangeLow</th>\n",
       "      <th>ficoRangeHigh</th>\n",
       "      <th>openAcc</th>\n",
       "      <th>pubRec</th>\n",
       "      <th>pubRecBankruptcies</th>\n",
       "      <th>revolBal</th>\n",
       "      <th>revolUtil</th>\n",
       "      <th>totalAcc</th>\n",
       "      <th>initialListStatus</th>\n",
       "      <th>applicationType</th>\n",
       "      <th>earliesCreditLine</th>\n",
       "      <th>title</th>\n",
       "      <th>policyCode</th>\n",
       "      <th>n0</th>\n",
       "      <th>n1</th>\n",
       "      <th>n2</th>\n",
       "      <th>n3</th>\n",
       "      <th>n4</th>\n",
       "      <th>n5</th>\n",
       "      <th>n6</th>\n",
       "      <th>n7</th>\n",
       "      <th>n8</th>\n",
       "      <th>n9</th>\n",
       "      <th>n10</th>\n",
       "      <th>n11</th>\n",
       "      <th>n12</th>\n",
       "      <th>n13</th>\n",
       "      <th>n14</th>\n",
       "      <th>issueDateDT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>800000</td>\n",
       "      <td>14000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10.99</td>\n",
       "      <td>458.28</td>\n",
       "      <td>B</td>\n",
       "      <td>B3</td>\n",
       "      <td>7027.0</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>0</td>\n",
       "      <td>80000.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-07-01</td>\n",
       "      <td>0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>21</td>\n",
       "      <td>10.56</td>\n",
       "      <td>1.0</td>\n",
       "      <td>715.0</td>\n",
       "      <td>719.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9846.0</td>\n",
       "      <td>30.7</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Nov-1974</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>800001</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>5</td>\n",
       "      <td>14.65</td>\n",
       "      <td>472.14</td>\n",
       "      <td>C</td>\n",
       "      <td>C5</td>\n",
       "      <td>60426.0</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>0</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>2</td>\n",
       "      <td>235.0</td>\n",
       "      <td>8</td>\n",
       "      <td>21.40</td>\n",
       "      <td>2.0</td>\n",
       "      <td>670.0</td>\n",
       "      <td>674.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8946.0</td>\n",
       "      <td>56.6</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Jul-2001</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>800002</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>19.99</td>\n",
       "      <td>445.91</td>\n",
       "      <td>D</td>\n",
       "      <td>D4</td>\n",
       "      <td>23547.0</td>\n",
       "      <td>2 years</td>\n",
       "      <td>1</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-10-01</td>\n",
       "      <td>0</td>\n",
       "      <td>526.0</td>\n",
       "      <td>20</td>\n",
       "      <td>33.50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>710.0</td>\n",
       "      <td>714.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>970.0</td>\n",
       "      <td>17.6</td>\n",
       "      <td>43.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Aug-2006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  loanAmnt  term  interestRate  installment grade subGrade  \\\n",
       "0  800000   14000.0     3         10.99       458.28     B       B3   \n",
       "1  800001   20000.0     5         14.65       472.14     C       C5   \n",
       "2  800002   12000.0     3         19.99       445.91     D       D4   \n",
       "\n",
       "   employmentTitle employmentLength  homeOwnership  annualIncome  \\\n",
       "0           7027.0        10+ years              0       80000.0   \n",
       "1          60426.0        10+ years              0       50000.0   \n",
       "2          23547.0          2 years              1       60000.0   \n",
       "\n",
       "   verificationStatus  issueDate  purpose  postCode  regionCode    dti  \\\n",
       "0                   0 2014-07-01        0     163.0          21  10.56   \n",
       "1                   0 2015-07-01        2     235.0           8  21.40   \n",
       "2                   2 2016-10-01        0     526.0          20  33.50   \n",
       "\n",
       "   delinquency_2years  ficoRangeLow  ficoRangeHigh  openAcc  pubRec  \\\n",
       "0                 1.0         715.0          719.0     17.0     0.0   \n",
       "1                 2.0         670.0          674.0      5.0     0.0   \n",
       "2                 0.0         710.0          714.0     12.0     0.0   \n",
       "\n",
       "   pubRecBankruptcies  revolBal  revolUtil  totalAcc  initialListStatus  \\\n",
       "0                 0.0    9846.0       30.7      29.0                  0   \n",
       "1                 0.0    8946.0       56.6      14.0                  0   \n",
       "2                 0.0     970.0       17.6      43.0                  1   \n",
       "\n",
       "   applicationType earliesCreditLine  title  policyCode   n0   n1   n2   n3  \\\n",
       "0                0          Nov-1974    0.0         1.0  1.0  4.0  6.0  6.0   \n",
       "1                0          Jul-2001    5.0         1.0  2.0  1.0  3.0  3.0   \n",
       "2                0          Aug-2006    0.0         1.0  0.0  1.0  4.0  4.0   \n",
       "\n",
       "    n4   n5    n6    n7    n8   n9   n10  n11  n12  n13  n14  issueDateDT  \n",
       "0  6.0  8.0   4.0  15.0  19.0  6.0  17.0  0.0  0.0  1.0  3.0         2587  \n",
       "1  1.0  1.0   3.0   3.0   9.0  3.0   5.0  0.0  0.0  2.0  2.0         2952  \n",
       "2  1.0  1.0  36.0   5.0   6.0  4.0  12.0  0.0  0.0  0.0  7.0         3410  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cd19ce2-ed65-4777-b04f-4334d6a43652",
   "metadata": {},
   "source": [
    "### Transform `employmentLength` into numerical values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1c49720b-5b69-493d-923f-fc2834a331ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1 year        55842\n",
       "10+ years    278860\n",
       "2 years       76742\n",
       "3 years       68149\n",
       "4 years       50932\n",
       "5 years       53186\n",
       "6 years       39575\n",
       "7 years       37622\n",
       "8 years       38551\n",
       "9 years       32225\n",
       "< 1 year      68316\n",
       "Name: employmentLength, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train['employmentLength'].value_counts(dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8038b48a-e095-4610-8e0d-b8ac43d5af72",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     16990\n",
       "1     14017\n",
       "2     19347\n",
       "3     17012\n",
       "4     12566\n",
       "5     13324\n",
       "6      9898\n",
       "7      9304\n",
       "8      9595\n",
       "9      8073\n",
       "10    69874\n",
       "Name: employmentLength, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def employmentLength_to_int(s):\n",
    "    if pd.isnull(s):\n",
    "        return s\n",
    "    else:\n",
    "        return np.int8(s.split()[0])\n",
    "for data in [data_train, data_test_a]:\n",
    "    data['employmentLength'].replace(to_replace='10+ years', value='10 years', inplace=True)\n",
    "    data['employmentLength'].replace('< 1 year', '0 years', inplace=True)\n",
    "    data['employmentLength'] = data['employmentLength'].apply(employmentLength_to_int)\n",
    "\n",
    "data['employmentLength'].value_counts(dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9037a95-4a16-46b8-aca8-7103d5cb0253",
   "metadata": {},
   "source": [
    "### Transform `earliesCreditLine` into numerical values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8ed9392f-8554-4f0c-a571-5f50d1ad96bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "447347    Mar-2009\n",
       "768855    Aug-2001\n",
       "590652    Aug-2000\n",
       "524062    Dec-2005\n",
       "419339    Dec-1991\n",
       "Name: earliesCreditLine, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train['earliesCreditLine'].sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "bf865203-584c-4132-b5b7-7efc8a6cb7e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# tranform earliesCreditLine into numerical values\n",
    "for data in [data_train, data_test_a]:\n",
    "    data['earliesCreditLine'] = data['earliesCreditLine'].apply(lambda s: int(s[-4:]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a3b40d2-a68f-49af-83a8-dee3b897d0a2",
   "metadata": {},
   "source": [
    "### Encode the categorical features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b443c6c0-1e85-4001-949d-2603e2a4a1e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "grade different values： 7\n",
      "subGrade different values： 35\n",
      "employmentTitle different values： 79282\n",
      "homeOwnership different values： 6\n",
      "verificationStatus different values： 3\n",
      "purpose different values： 14\n",
      "postCode different values： 889\n",
      "regionCode different values： 51\n",
      "applicationType different values： 2\n",
      "initialListStatus different values： 2\n",
      "title different values： 12058\n",
      "policyCode different values： 1\n"
     ]
    }
   ],
   "source": [
    "cate_features = ['grade', 'subGrade', 'employmentTitle', 'homeOwnership', 'verificationStatus', 'purpose', 'postCode', 'regionCode', \\\n",
    "                 'applicationType', 'initialListStatus', 'title', 'policyCode']\n",
    "for f in cate_features:\n",
    "    print(f, 'different values：', data[f].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "109df3a7-4624-48b9-8f4f-e496ccbd572b",
   "metadata": {},
   "outputs": [],
   "source": [
    "for data in [data_train, data_test_a]:\n",
    "    data['grade'] = data['grade'].map({'A':1,'B':2,'C':3,'D':4,'E':5,'F':6,'G':7})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ce81a4bc-5dcd-49ca-a492-9f86e7bbb60b",
   "metadata": {},
   "outputs": [],
   "source": [
    "for data in [data_train, data_test_a]:\n",
    "    data = pd.get_dummies(data, columns=['subGrade', 'homeOwnership', 'verificationStatus', 'purpose', 'regionCode'], drop_first=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e156ab20-8cf9-47bc-b4ab-52fd4df96860",
   "metadata": {},
   "source": [
    "### Outliers processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "44403f68-f05f-41c0-b94c-20b538c14fc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_outliers_by_3segama(data,fea):\n",
    "    data_std = np.std(data[fea])\n",
    "    data_mean = np.mean(data[fea])\n",
    "    outliers_cut_off = data_std * 3\n",
    "    lower_rule = data_mean - outliers_cut_off\n",
    "    upper_rule = data_mean + outliers_cut_off\n",
    "    data[fea + '_outliers'] = data[fea].apply(lambda x:str('ExceptionValue') if x > upper_rule or x < lower_rule else 'NormalValue')\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a4179c85-9504-4834-bb9c-2bd60ccfdaa3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NormalValue    800000\n",
      "Name: id_outliers, dtype: int64\n",
      "id_outliers\n",
      "NormalValue    159610\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue    800000\n",
      "Name: loanAmnt_outliers, dtype: int64\n",
      "loanAmnt_outliers\n",
      "NormalValue    159610\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue    800000\n",
      "Name: term_outliers, dtype: int64\n",
      "term_outliers\n",
      "NormalValue    159610\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       794259\n",
      "ExceptionValue      5741\n",
      "Name: interestRate_outliers, dtype: int64\n",
      "interestRate_outliers\n",
      "ExceptionValue      2916\n",
      "NormalValue       156694\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       792046\n",
      "ExceptionValue      7954\n",
      "Name: installment_outliers, dtype: int64\n",
      "installment_outliers\n",
      "ExceptionValue      2152\n",
      "NormalValue       157458\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue    800000\n",
      "Name: employmentTitle_outliers, dtype: int64\n",
      "employmentTitle_outliers\n",
      "NormalValue    159610\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       799701\n",
      "ExceptionValue       299\n",
      "Name: homeOwnership_outliers, dtype: int64\n",
      "homeOwnership_outliers\n",
      "ExceptionValue        62\n",
      "NormalValue       159548\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       793973\n",
      "ExceptionValue      6027\n",
      "Name: annualIncome_outliers, dtype: int64\n",
      "annualIncome_outliers\n",
      "ExceptionValue       756\n",
      "NormalValue       158854\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue    800000\n",
      "Name: verificationStatus_outliers, dtype: int64\n",
      "verificationStatus_outliers\n",
      "NormalValue    159610\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       783003\n",
      "ExceptionValue     16997\n",
      "Name: purpose_outliers, dtype: int64\n",
      "purpose_outliers\n",
      "ExceptionValue      3635\n",
      "NormalValue       155975\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       798931\n",
      "ExceptionValue      1069\n",
      "Name: postCode_outliers, dtype: int64\n",
      "postCode_outliers\n",
      "ExceptionValue       221\n",
      "NormalValue       159389\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       799994\n",
      "ExceptionValue         6\n",
      "Name: regionCode_outliers, dtype: int64\n",
      "regionCode_outliers\n",
      "ExceptionValue         1\n",
      "NormalValue       159609\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       798440\n",
      "ExceptionValue      1560\n",
      "Name: dti_outliers, dtype: int64\n",
      "dti_outliers\n",
      "ExceptionValue       466\n",
      "NormalValue       159144\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       778245\n",
      "ExceptionValue     21755\n",
      "Name: delinquency_2years_outliers, dtype: int64\n",
      "delinquency_2years_outliers\n",
      "ExceptionValue      5089\n",
      "NormalValue       154521\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       788261\n",
      "ExceptionValue     11739\n",
      "Name: ficoRangeLow_outliers, dtype: int64\n",
      "ficoRangeLow_outliers\n",
      "ExceptionValue       778\n",
      "NormalValue       158832\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       788261\n",
      "ExceptionValue     11739\n",
      "Name: ficoRangeHigh_outliers, dtype: int64\n",
      "ficoRangeHigh_outliers\n",
      "ExceptionValue       778\n",
      "NormalValue       158832\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       790889\n",
      "ExceptionValue      9111\n",
      "Name: openAcc_outliers, dtype: int64\n",
      "openAcc_outliers\n",
      "ExceptionValue      2195\n",
      "NormalValue       157415\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       792471\n",
      "ExceptionValue      7529\n",
      "Name: pubRec_outliers, dtype: int64\n",
      "pubRec_outliers\n",
      "ExceptionValue      1701\n",
      "NormalValue       157909\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       794120\n",
      "ExceptionValue      5880\n",
      "Name: pubRecBankruptcies_outliers, dtype: int64\n",
      "pubRecBankruptcies_outliers\n",
      "ExceptionValue      1423\n",
      "NormalValue       158187\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       790001\n",
      "ExceptionValue      9999\n",
      "Name: revolBal_outliers, dtype: int64\n",
      "revolBal_outliers\n",
      "ExceptionValue      1359\n",
      "NormalValue       158251\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       799948\n",
      "ExceptionValue        52\n",
      "Name: revolUtil_outliers, dtype: int64\n",
      "revolUtil_outliers\n",
      "ExceptionValue        23\n",
      "NormalValue       159587\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       791663\n",
      "ExceptionValue      8337\n",
      "Name: totalAcc_outliers, dtype: int64\n",
      "totalAcc_outliers\n",
      "ExceptionValue      1668\n",
      "NormalValue       157942\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue    800000\n",
      "Name: initialListStatus_outliers, dtype: int64\n",
      "initialListStatus_outliers\n",
      "NormalValue    159610\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       784586\n",
      "ExceptionValue     15414\n",
      "Name: applicationType_outliers, dtype: int64\n",
      "applicationType_outliers\n",
      "ExceptionValue      3875\n",
      "NormalValue       155735\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       775134\n",
      "ExceptionValue     24866\n",
      "Name: title_outliers, dtype: int64\n",
      "title_outliers\n",
      "ExceptionValue      3900\n",
      "NormalValue       155710\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue    800000\n",
      "Name: policyCode_outliers, dtype: int64\n",
      "policyCode_outliers\n",
      "NormalValue    159610\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       782773\n",
      "ExceptionValue     17227\n",
      "Name: n0_outliers, dtype: int64\n",
      "n0_outliers\n",
      "ExceptionValue      3485\n",
      "NormalValue       156125\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       790500\n",
      "ExceptionValue      9500\n",
      "Name: n1_outliers, dtype: int64\n",
      "n1_outliers\n",
      "ExceptionValue      2491\n",
      "NormalValue       157119\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       789067\n",
      "ExceptionValue     10933\n",
      "Name: n2_outliers, dtype: int64\n",
      "n2_outliers\n",
      "ExceptionValue      3205\n",
      "NormalValue       156405\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       789067\n",
      "ExceptionValue     10933\n",
      "Name: n3_outliers, dtype: int64\n",
      "n3_outliers\n",
      "ExceptionValue      3205\n",
      "NormalValue       156405\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       788660\n",
      "ExceptionValue     11340\n",
      "Name: n4_outliers, dtype: int64\n",
      "n4_outliers\n",
      "ExceptionValue      2476\n",
      "NormalValue       157134\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       790355\n",
      "ExceptionValue      9645\n",
      "Name: n5_outliers, dtype: int64\n",
      "n5_outliers\n",
      "ExceptionValue      1858\n",
      "NormalValue       157752\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       786006\n",
      "ExceptionValue     13994\n",
      "Name: n6_outliers, dtype: int64\n",
      "n6_outliers\n",
      "ExceptionValue      3182\n",
      "NormalValue       156428\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       788430\n",
      "ExceptionValue     11570\n",
      "Name: n7_outliers, dtype: int64\n",
      "n7_outliers\n",
      "ExceptionValue      2746\n",
      "NormalValue       156864\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       789625\n",
      "ExceptionValue     10375\n",
      "Name: n8_outliers, dtype: int64\n",
      "n8_outliers\n",
      "ExceptionValue      2131\n",
      "NormalValue       157479\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       786384\n",
      "ExceptionValue     13616\n",
      "Name: n9_outliers, dtype: int64\n",
      "n9_outliers\n",
      "ExceptionValue      3953\n",
      "NormalValue       155657\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       788979\n",
      "ExceptionValue     11021\n",
      "Name: n10_outliers, dtype: int64\n",
      "n10_outliers\n",
      "ExceptionValue      2639\n",
      "NormalValue       156971\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       799434\n",
      "ExceptionValue       566\n",
      "Name: n11_outliers, dtype: int64\n",
      "n11_outliers\n",
      "ExceptionValue       112\n",
      "NormalValue       159498\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       797585\n",
      "ExceptionValue      2415\n",
      "Name: n12_outliers, dtype: int64\n",
      "n12_outliers\n",
      "ExceptionValue       545\n",
      "NormalValue       159065\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       788907\n",
      "ExceptionValue     11093\n",
      "Name: n13_outliers, dtype: int64\n",
      "n13_outliers\n",
      "ExceptionValue      2482\n",
      "NormalValue       157128\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n",
      "NormalValue       788884\n",
      "ExceptionValue     11116\n",
      "Name: n14_outliers, dtype: int64\n",
      "n14_outliers\n",
      "ExceptionValue      3364\n",
      "NormalValue       156246\n",
      "Name: isDefault, dtype: int64\n",
      "------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for fea in numerical_fea:\n",
    "    data_train = find_outliers_by_3segama(data_train,fea)\n",
    "    print(data_train[fea + '_outliers'].value_counts())\n",
    "    print(data_train.groupby(fea + '_outliers')['isDefault'].sum())\n",
    "    print('-' * 60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "282ebd19-c54a-46cf-936c-70867e883fc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter the exception values\n",
    "for fea in numerical_fea:\n",
    "    data_train = data_train[data_train[fea+'_outliers']=='NormalValue']\n",
    "    data_train = data_train.reset_index(drop=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "639e556d-bb97-4171-a91c-ff7c93345f57",
   "metadata": {},
   "source": [
    "### Feature binning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "3d24156e-f647-4db1-ac5b-40014040e081",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Feature binning\n",
    "data['loanAmnt_bin1'] = np.floor_divide(data['loanAmnt'], 1000)\n",
    "data['loanAmnt_bin2'] = np.floor(np.log10(data['loanAmnt']))\n",
    "data['loanAmnt_bin3'] = pd.qcut(data['loanAmnt'], 10, labels=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a2326faf-4ccb-457f-91a7-1a793e8c6616",
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in ['grade', 'subGrade']: \n",
    "    temp_dict = data_train.groupby([col])['isDefault'].agg(['mean']).reset_index().rename(columns={'mean': col + '_target_mean'})\n",
    "    temp_dict.index = temp_dict[col].values\n",
    "    temp_dict = temp_dict[col + '_target_mean'].to_dict()\n",
    "    data_train[col + '_target_mean'] = data_train[col].map(temp_dict)\n",
    "    data_test_a[col + '_target_mean'] = data_test_a[col].map(temp_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d72004f-b048-4c9c-8ed9-77d7c1de4390",
   "metadata": {},
   "source": [
    "### Feature interaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ec707879-80db-4aae-9326-7fa531f36822",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Mean and std\n",
    "for df in [data_train, data_test_a]:\n",
    "    for item in ['n0','n1','n2','n4','n5','n6','n7','n8','n9','n10','n11','n12','n13','n14']:\n",
    "        df['grade_to_mean_' + item] = df['grade'] / df.groupby([item])['grade'].transform('mean')\n",
    "        df['grade_to_std_' + item] = df['grade'] / df.groupby([item])['grade'].transform('std')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3448cf1-a340-4f47-aeff-0946adfa23c1",
   "metadata": {},
   "source": [
    "### High dimensional feature encoding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "a7441abb-bef7-4ac4-8173-77c9b0c53a84",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 4/4 [00:04<00:00,  1.19s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Label Encoding Completed\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# Label-encoding: subGrade, postCode, title\n",
    "for col in tqdm(['employmentTitle', 'postCode', 'title','subGrade']):\n",
    "    le = LabelEncoder()\n",
    "    le.fit(list(data_train[col].astype(str).values) + list(data_test_a[col].astype(str).values))\n",
    "    data_train[col] = le.transform(list(data_train[col].astype(str).values))\n",
    "    data_test_a[col] = le.transform(list(data_test_a[col].astype(str).values))\n",
    "print('Label Encoding Completed')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5411c688-1b5b-4d24-9597-a207e946b312",
   "metadata": {},
   "source": [
    "### Remove useless features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "067e6b96-fd33-4dee-b739-3a68158c85d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove issueDate, id\n",
    "for data in [data_train, data_test_a]:\n",
    "    data.drop(['issueDate','id'], axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "3a87573c-847d-41f2-8fb7-aa136db696da",
   "metadata": {},
   "outputs": [],
   "source": [
    "features = [f for f in data_train.columns if f not in ['id','issueDate'] and '_outliers' not in f]\n",
    "fg_data_train = data_train[features]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "33f1e1bb-6533-4d24-ac84-2d61d4479ffb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loanAmnt</th>\n",
       "      <th>term</th>\n",
       "      <th>interestRate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>subGrade</th>\n",
       "      <th>employmentTitle</th>\n",
       "      <th>employmentLength</th>\n",
       "      <th>homeOwnership</th>\n",
       "      <th>annualIncome</th>\n",
       "      <th>verificationStatus</th>\n",
       "      <th>isDefault</th>\n",
       "      <th>purpose</th>\n",
       "      <th>postCode</th>\n",
       "      <th>regionCode</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinquency_2years</th>\n",
       "      <th>ficoRangeLow</th>\n",
       "      <th>ficoRangeHigh</th>\n",
       "      <th>openAcc</th>\n",
       "      <th>pubRec</th>\n",
       "      <th>pubRecBankruptcies</th>\n",
       "      <th>revolBal</th>\n",
       "      <th>revolUtil</th>\n",
       "      <th>totalAcc</th>\n",
       "      <th>initialListStatus</th>\n",
       "      <th>applicationType</th>\n",
       "      <th>earliesCreditLine</th>\n",
       "      <th>title</th>\n",
       "      <th>policyCode</th>\n",
       "      <th>n0</th>\n",
       "      <th>n1</th>\n",
       "      <th>n2</th>\n",
       "      <th>n3</th>\n",
       "      <th>n4</th>\n",
       "      <th>n5</th>\n",
       "      <th>n6</th>\n",
       "      <th>n7</th>\n",
       "      <th>n8</th>\n",
       "      <th>n9</th>\n",
       "      <th>n10</th>\n",
       "      <th>n11</th>\n",
       "      <th>n12</th>\n",
       "      <th>n13</th>\n",
       "      <th>n14</th>\n",
       "      <th>issueDateDT</th>\n",
       "      <th>grade_target_mean</th>\n",
       "      <th>subGrade_target_mean</th>\n",
       "      <th>grade_to_mean_n0</th>\n",
       "      <th>grade_to_std_n0</th>\n",
       "      <th>grade_to_mean_n1</th>\n",
       "      <th>grade_to_std_n1</th>\n",
       "      <th>grade_to_mean_n2</th>\n",
       "      <th>grade_to_std_n2</th>\n",
       "      <th>grade_to_mean_n4</th>\n",
       "      <th>grade_to_std_n4</th>\n",
       "      <th>grade_to_mean_n5</th>\n",
       "      <th>grade_to_std_n5</th>\n",
       "      <th>grade_to_mean_n6</th>\n",
       "      <th>grade_to_std_n6</th>\n",
       "      <th>grade_to_mean_n7</th>\n",
       "      <th>grade_to_std_n7</th>\n",
       "      <th>grade_to_mean_n8</th>\n",
       "      <th>grade_to_std_n8</th>\n",
       "      <th>grade_to_mean_n9</th>\n",
       "      <th>grade_to_std_n9</th>\n",
       "      <th>grade_to_mean_n10</th>\n",
       "      <th>grade_to_std_n10</th>\n",
       "      <th>grade_to_mean_n11</th>\n",
       "      <th>grade_to_std_n11</th>\n",
       "      <th>grade_to_mean_n12</th>\n",
       "      <th>grade_to_std_n12</th>\n",
       "      <th>grade_to_mean_n13</th>\n",
       "      <th>grade_to_std_n13</th>\n",
       "      <th>grade_to_mean_n14</th>\n",
       "      <th>grade_to_std_n14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35000.0</td>\n",
       "      <td>5</td>\n",
       "      <td>19.52</td>\n",
       "      <td>917.97</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>161280</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>110000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>43</td>\n",
       "      <td>32</td>\n",
       "      <td>17.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>730.0</td>\n",
       "      <td>734.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24178.0</td>\n",
       "      <td>48.9</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2001</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2587</td>\n",
       "      <td>0.386234</td>\n",
       "      <td>0.380444</td>\n",
       "      <td>1.876011</td>\n",
       "      <td>3.992386</td>\n",
       "      <td>1.874620</td>\n",
       "      <td>4.053876</td>\n",
       "      <td>1.942294</td>\n",
       "      <td>4.023418</td>\n",
       "      <td>1.869160</td>\n",
       "      <td>3.948124</td>\n",
       "      <td>1.897562</td>\n",
       "      <td>4.055665</td>\n",
       "      <td>1.865760</td>\n",
       "      <td>4.017884</td>\n",
       "      <td>1.840872</td>\n",
       "      <td>4.074681</td>\n",
       "      <td>1.851544</td>\n",
       "      <td>4.040923</td>\n",
       "      <td>1.938318</td>\n",
       "      <td>4.024912</td>\n",
       "      <td>1.842210</td>\n",
       "      <td>4.108917</td>\n",
       "      <td>1.852810</td>\n",
       "      <td>4.009823</td>\n",
       "      <td>1.852810</td>\n",
       "      <td>4.009823</td>\n",
       "      <td>1.857394</td>\n",
       "      <td>4.005352</td>\n",
       "      <td>1.856379</td>\n",
       "      <td>3.991791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18000.0</td>\n",
       "      <td>5</td>\n",
       "      <td>18.49</td>\n",
       "      <td>461.90</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>89538</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>46000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td>18</td>\n",
       "      <td>27.83</td>\n",
       "      <td>0.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>704.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15096.0</td>\n",
       "      <td>38.9</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2002</td>\n",
       "      <td>5768</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1888</td>\n",
       "      <td>0.304227</td>\n",
       "      <td>0.298190</td>\n",
       "      <td>1.500809</td>\n",
       "      <td>3.193909</td>\n",
       "      <td>1.502905</td>\n",
       "      <td>3.185919</td>\n",
       "      <td>1.504054</td>\n",
       "      <td>3.173189</td>\n",
       "      <td>1.567352</td>\n",
       "      <td>3.204484</td>\n",
       "      <td>1.511316</td>\n",
       "      <td>3.139166</td>\n",
       "      <td>1.515599</td>\n",
       "      <td>3.098975</td>\n",
       "      <td>1.500817</td>\n",
       "      <td>3.139721</td>\n",
       "      <td>1.517874</td>\n",
       "      <td>3.086106</td>\n",
       "      <td>1.504140</td>\n",
       "      <td>3.174194</td>\n",
       "      <td>1.484104</td>\n",
       "      <td>3.173687</td>\n",
       "      <td>1.482248</td>\n",
       "      <td>3.207858</td>\n",
       "      <td>1.482248</td>\n",
       "      <td>3.207858</td>\n",
       "      <td>1.485915</td>\n",
       "      <td>3.204282</td>\n",
       "      <td>1.485103</td>\n",
       "      <td>3.193433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12000.0</td>\n",
       "      <td>5</td>\n",
       "      <td>16.99</td>\n",
       "      <td>298.17</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>159367</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>74000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>265</td>\n",
       "      <td>14</td>\n",
       "      <td>22.77</td>\n",
       "      <td>0.0</td>\n",
       "      <td>675.0</td>\n",
       "      <td>679.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4606.0</td>\n",
       "      <td>51.8</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2006</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3044</td>\n",
       "      <td>0.304227</td>\n",
       "      <td>0.302541</td>\n",
       "      <td>1.500809</td>\n",
       "      <td>3.193909</td>\n",
       "      <td>1.360761</td>\n",
       "      <td>2.998190</td>\n",
       "      <td>1.532981</td>\n",
       "      <td>3.241462</td>\n",
       "      <td>1.273891</td>\n",
       "      <td>3.071276</td>\n",
       "      <td>1.162371</td>\n",
       "      <td>3.176718</td>\n",
       "      <td>1.480241</td>\n",
       "      <td>3.125317</td>\n",
       "      <td>1.472698</td>\n",
       "      <td>3.259745</td>\n",
       "      <td>1.406712</td>\n",
       "      <td>3.254085</td>\n",
       "      <td>1.530998</td>\n",
       "      <td>3.244609</td>\n",
       "      <td>1.504230</td>\n",
       "      <td>3.089208</td>\n",
       "      <td>1.482248</td>\n",
       "      <td>3.207858</td>\n",
       "      <td>1.482248</td>\n",
       "      <td>3.207858</td>\n",
       "      <td>1.485915</td>\n",
       "      <td>3.204282</td>\n",
       "      <td>1.315111</td>\n",
       "      <td>3.146801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2050.0</td>\n",
       "      <td>3</td>\n",
       "      <td>7.69</td>\n",
       "      <td>63.95</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>59830</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>465</td>\n",
       "      <td>14</td>\n",
       "      <td>17.49</td>\n",
       "      <td>0.0</td>\n",
       "      <td>755.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3111.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2006</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2679</td>\n",
       "      <td>0.059838</td>\n",
       "      <td>0.065532</td>\n",
       "      <td>0.375202</td>\n",
       "      <td>0.798477</td>\n",
       "      <td>0.368239</td>\n",
       "      <td>0.796491</td>\n",
       "      <td>0.383245</td>\n",
       "      <td>0.810366</td>\n",
       "      <td>0.380622</td>\n",
       "      <td>0.806605</td>\n",
       "      <td>0.384972</td>\n",
       "      <td>0.802575</td>\n",
       "      <td>0.368526</td>\n",
       "      <td>0.819126</td>\n",
       "      <td>0.369865</td>\n",
       "      <td>0.798404</td>\n",
       "      <td>0.377964</td>\n",
       "      <td>0.799464</td>\n",
       "      <td>0.382750</td>\n",
       "      <td>0.811152</td>\n",
       "      <td>0.370128</td>\n",
       "      <td>0.799459</td>\n",
       "      <td>0.370562</td>\n",
       "      <td>0.801965</td>\n",
       "      <td>0.370562</td>\n",
       "      <td>0.801965</td>\n",
       "      <td>0.371479</td>\n",
       "      <td>0.801070</td>\n",
       "      <td>0.344287</td>\n",
       "      <td>0.793451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11500.0</td>\n",
       "      <td>3</td>\n",
       "      <td>14.98</td>\n",
       "      <td>398.54</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>85242</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>32.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>665.0</td>\n",
       "      <td>669.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14021.0</td>\n",
       "      <td>59.7</td>\n",
       "      <td>33.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1994</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2406</td>\n",
       "      <td>0.224522</td>\n",
       "      <td>0.224686</td>\n",
       "      <td>1.125607</td>\n",
       "      <td>2.395431</td>\n",
       "      <td>1.113406</td>\n",
       "      <td>2.430896</td>\n",
       "      <td>1.133984</td>\n",
       "      <td>2.439745</td>\n",
       "      <td>1.121496</td>\n",
       "      <td>2.368874</td>\n",
       "      <td>1.197930</td>\n",
       "      <td>2.401168</td>\n",
       "      <td>1.120956</td>\n",
       "      <td>2.388727</td>\n",
       "      <td>1.106851</td>\n",
       "      <td>2.450979</td>\n",
       "      <td>1.144817</td>\n",
       "      <td>2.403154</td>\n",
       "      <td>1.133458</td>\n",
       "      <td>2.441340</td>\n",
       "      <td>1.104961</td>\n",
       "      <td>2.446307</td>\n",
       "      <td>1.111686</td>\n",
       "      <td>2.405894</td>\n",
       "      <td>1.111686</td>\n",
       "      <td>2.405894</td>\n",
       "      <td>1.114436</td>\n",
       "      <td>2.403211</td>\n",
       "      <td>1.113827</td>\n",
       "      <td>2.395075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>12.99</td>\n",
       "      <td>404.27</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>65718</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>770</td>\n",
       "      <td>13</td>\n",
       "      <td>19.22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>690.0</td>\n",
       "      <td>694.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27176.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1994</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3257</td>\n",
       "      <td>0.224522</td>\n",
       "      <td>0.204005</td>\n",
       "      <td>1.125607</td>\n",
       "      <td>2.395431</td>\n",
       "      <td>1.085997</td>\n",
       "      <td>2.408741</td>\n",
       "      <td>0.984707</td>\n",
       "      <td>2.361605</td>\n",
       "      <td>1.141867</td>\n",
       "      <td>2.419815</td>\n",
       "      <td>1.133487</td>\n",
       "      <td>2.354374</td>\n",
       "      <td>1.100101</td>\n",
       "      <td>2.459716</td>\n",
       "      <td>1.119411</td>\n",
       "      <td>2.396658</td>\n",
       "      <td>1.136053</td>\n",
       "      <td>2.409156</td>\n",
       "      <td>1.011351</td>\n",
       "      <td>2.376224</td>\n",
       "      <td>1.124941</td>\n",
       "      <td>2.384061</td>\n",
       "      <td>1.111686</td>\n",
       "      <td>2.405894</td>\n",
       "      <td>1.111686</td>\n",
       "      <td>2.405894</td>\n",
       "      <td>1.114436</td>\n",
       "      <td>2.403211</td>\n",
       "      <td>0.923430</td>\n",
       "      <td>2.361914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>9.99</td>\n",
       "      <td>774.30</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>209276</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>8</td>\n",
       "      <td>5.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>690.0</td>\n",
       "      <td>694.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4334.0</td>\n",
       "      <td>68.8</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1983</td>\n",
       "      <td>18780</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2983</td>\n",
       "      <td>0.131210</td>\n",
       "      <td>0.128111</td>\n",
       "      <td>0.707941</td>\n",
       "      <td>1.635584</td>\n",
       "      <td>0.736477</td>\n",
       "      <td>1.592982</td>\n",
       "      <td>0.766491</td>\n",
       "      <td>1.620731</td>\n",
       "      <td>0.720818</td>\n",
       "      <td>1.621383</td>\n",
       "      <td>0.755658</td>\n",
       "      <td>1.569583</td>\n",
       "      <td>0.757800</td>\n",
       "      <td>1.549487</td>\n",
       "      <td>0.738697</td>\n",
       "      <td>1.625010</td>\n",
       "      <td>0.757368</td>\n",
       "      <td>1.606104</td>\n",
       "      <td>0.765499</td>\n",
       "      <td>1.622304</td>\n",
       "      <td>0.736884</td>\n",
       "      <td>1.643567</td>\n",
       "      <td>0.741124</td>\n",
       "      <td>1.603929</td>\n",
       "      <td>0.741124</td>\n",
       "      <td>1.603929</td>\n",
       "      <td>0.742958</td>\n",
       "      <td>1.602141</td>\n",
       "      <td>0.742552</td>\n",
       "      <td>1.596716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>7.91</td>\n",
       "      <td>500.72</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8198</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>76</td>\n",
       "      <td>8</td>\n",
       "      <td>38.95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>710.0</td>\n",
       "      <td>714.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19023.0</td>\n",
       "      <td>60.8</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2011</td>\n",
       "      <td>16334</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3136</td>\n",
       "      <td>0.059838</td>\n",
       "      <td>0.083522</td>\n",
       "      <td>0.375202</td>\n",
       "      <td>0.798477</td>\n",
       "      <td>0.371135</td>\n",
       "      <td>0.810299</td>\n",
       "      <td>0.376013</td>\n",
       "      <td>0.793297</td>\n",
       "      <td>0.373832</td>\n",
       "      <td>0.789625</td>\n",
       "      <td>0.368325</td>\n",
       "      <td>0.815212</td>\n",
       "      <td>0.366700</td>\n",
       "      <td>0.819905</td>\n",
       "      <td>0.375204</td>\n",
       "      <td>0.784930</td>\n",
       "      <td>0.364666</td>\n",
       "      <td>0.813245</td>\n",
       "      <td>0.376035</td>\n",
       "      <td>0.793549</td>\n",
       "      <td>0.368003</td>\n",
       "      <td>0.809138</td>\n",
       "      <td>0.370562</td>\n",
       "      <td>0.801965</td>\n",
       "      <td>0.370562</td>\n",
       "      <td>0.801965</td>\n",
       "      <td>0.371479</td>\n",
       "      <td>0.801070</td>\n",
       "      <td>0.395135</td>\n",
       "      <td>0.846111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10.49</td>\n",
       "      <td>194.99</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>115263</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>77000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>106</td>\n",
       "      <td>38</td>\n",
       "      <td>17.27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>660.0</td>\n",
       "      <td>664.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1996</td>\n",
       "      <td>18780</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3533</td>\n",
       "      <td>0.131210</td>\n",
       "      <td>0.109461</td>\n",
       "      <td>0.750404</td>\n",
       "      <td>1.596954</td>\n",
       "      <td>0.736477</td>\n",
       "      <td>1.592982</td>\n",
       "      <td>0.755989</td>\n",
       "      <td>1.626497</td>\n",
       "      <td>0.720818</td>\n",
       "      <td>1.621383</td>\n",
       "      <td>0.769944</td>\n",
       "      <td>1.605151</td>\n",
       "      <td>0.739618</td>\n",
       "      <td>1.580526</td>\n",
       "      <td>0.746274</td>\n",
       "      <td>1.597772</td>\n",
       "      <td>0.788374</td>\n",
       "      <td>1.610142</td>\n",
       "      <td>0.755638</td>\n",
       "      <td>1.627560</td>\n",
       "      <td>0.749961</td>\n",
       "      <td>1.589374</td>\n",
       "      <td>0.741124</td>\n",
       "      <td>1.603929</td>\n",
       "      <td>0.741124</td>\n",
       "      <td>1.603929</td>\n",
       "      <td>0.742958</td>\n",
       "      <td>1.602141</td>\n",
       "      <td>0.846155</td>\n",
       "      <td>1.753293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10375.0</td>\n",
       "      <td>5</td>\n",
       "      <td>15.61</td>\n",
       "      <td>250.16</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>74728</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>58000.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>437</td>\n",
       "      <td>36</td>\n",
       "      <td>21.02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>705.0</td>\n",
       "      <td>709.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36609.0</td>\n",
       "      <td>61.1</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2002</td>\n",
       "      <td>18780</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2526</td>\n",
       "      <td>0.304227</td>\n",
       "      <td>0.279444</td>\n",
       "      <td>1.500809</td>\n",
       "      <td>3.193909</td>\n",
       "      <td>1.502905</td>\n",
       "      <td>3.185919</td>\n",
       "      <td>1.511979</td>\n",
       "      <td>3.252993</td>\n",
       "      <td>1.494754</td>\n",
       "      <td>3.218213</td>\n",
       "      <td>1.473298</td>\n",
       "      <td>3.260850</td>\n",
       "      <td>1.479236</td>\n",
       "      <td>3.161051</td>\n",
       "      <td>1.492548</td>\n",
       "      <td>3.195544</td>\n",
       "      <td>1.497336</td>\n",
       "      <td>3.234727</td>\n",
       "      <td>1.511277</td>\n",
       "      <td>3.255120</td>\n",
       "      <td>1.496655</td>\n",
       "      <td>3.146687</td>\n",
       "      <td>1.482248</td>\n",
       "      <td>3.207858</td>\n",
       "      <td>1.482248</td>\n",
       "      <td>3.207858</td>\n",
       "      <td>1.485915</td>\n",
       "      <td>3.204282</td>\n",
       "      <td>1.485103</td>\n",
       "      <td>3.193433</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loanAmnt  term  interestRate  installment  grade  subGrade  \\\n",
       "0   35000.0     5         19.52       917.97      5        21   \n",
       "1   18000.0     5         18.49       461.90      4        16   \n",
       "2   12000.0     5         16.99       298.17      4        17   \n",
       "3    2050.0     3          7.69        63.95      1         3   \n",
       "4   11500.0     3         14.98       398.54      3        12   \n",
       "5   12000.0     3         12.99       404.27      3        11   \n",
       "6   24000.0     3          9.99       774.30      2         7   \n",
       "7   16000.0     3          7.91       500.72      1         4   \n",
       "8    6000.0     3         10.49       194.99      2         6   \n",
       "9   10375.0     5         15.61       250.16      4        15   \n",
       "\n",
       "   employmentTitle  employmentLength  homeOwnership  annualIncome  \\\n",
       "0           161280                 2              2      110000.0   \n",
       "1            89538                 5              0       46000.0   \n",
       "2           159367                 8              0       74000.0   \n",
       "3            59830                 9              0       35000.0   \n",
       "4            85242                 1              1       30000.0   \n",
       "5            65718                 5              2       60000.0   \n",
       "6           209276                10              0      150000.0   \n",
       "7             8198                 2              1       50000.0   \n",
       "8           115263                 2              0       77000.0   \n",
       "9            74728                 9              0       58000.0   \n",
       "\n",
       "   verificationStatus  isDefault  purpose  postCode  regionCode    dti  \\\n",
       "0                   2          1        1        43          32  17.05   \n",
       "1                   2          0        0        64          18  27.83   \n",
       "2                   2          0        0       265          14  22.77   \n",
       "3                   0          0        0       465          14  17.49   \n",
       "4                   2          0        0         3           4  32.60   \n",
       "5                   1          1        0       770          13  19.22   \n",
       "6                   1          0        2        40           8   5.68   \n",
       "7                   0          0        4        76           8  38.95   \n",
       "8                   1          0        2       106          38  17.27   \n",
       "9                   0          0        2       437          36  21.02   \n",
       "\n",
       "   delinquency_2years  ficoRangeLow  ficoRangeHigh  openAcc  pubRec  \\\n",
       "0                 0.0         730.0          734.0      7.0     0.0   \n",
       "1                 0.0         700.0          704.0     13.0     0.0   \n",
       "2                 0.0         675.0          679.0     11.0     0.0   \n",
       "3                 0.0         755.0          759.0     12.0     0.0   \n",
       "4                 0.0         665.0          669.0      8.0     1.0   \n",
       "5                 0.0         690.0          694.0     15.0     0.0   \n",
       "6                 0.0         690.0          694.0      7.0     0.0   \n",
       "7                 0.0         710.0          714.0      9.0     0.0   \n",
       "8                 0.0         660.0          664.0     16.0     1.0   \n",
       "9                 0.0         705.0          709.0     16.0     0.0   \n",
       "\n",
       "   pubRecBankruptcies  revolBal  revolUtil  totalAcc  initialListStatus  \\\n",
       "0                 0.0   24178.0       48.9      27.0                  0   \n",
       "1                 0.0   15096.0       38.9      18.0                  1   \n",
       "2                 0.0    4606.0       51.8      27.0                  0   \n",
       "3                 0.0    3111.0        8.5      23.0                  0   \n",
       "4                 1.0   14021.0       59.7      33.0                  1   \n",
       "5                 0.0   27176.0       46.0      21.0                  1   \n",
       "6                 0.0    4334.0       68.8      25.0                  0   \n",
       "7                 0.0   19023.0       60.8      11.0                  0   \n",
       "8                 1.0     220.0        3.6      49.0                  0   \n",
       "9                 0.0   36609.0       61.1      33.0                  0   \n",
       "\n",
       "   applicationType  earliesCreditLine  title  policyCode   n0   n1    n2  \\\n",
       "0                0               2001      1         1.0  0.0  2.0   2.0   \n",
       "1                0               2002   5768         1.0  0.0  3.0   5.0   \n",
       "2                0               2006      0         1.0  0.0  0.0   3.0   \n",
       "3                0               2006      0         1.0  0.0  1.0   3.0   \n",
       "4                0               1994      0         1.0  0.0  4.0   4.0   \n",
       "5                0               1994      0         1.0  0.0  7.0  13.0   \n",
       "6                0               1983  18780         1.0  1.0  1.0   3.0   \n",
       "7                0               2011  16334         1.0  0.0  4.0   5.0   \n",
       "8                0               1996  18780         1.0  0.0  1.0   4.0   \n",
       "9                0               2002  18780         1.0  0.0  3.0   4.0   \n",
       "\n",
       "     n3    n4    n5    n6    n7    n8    n9   n10  n11  n12  n13  n14  \\\n",
       "0   2.0   4.0   9.0   8.0   4.0  12.0   2.0   7.0  0.0  0.0  0.0  2.0   \n",
       "1   5.0  10.0   7.0   7.0   7.0  13.0   5.0  13.0  0.0  0.0  0.0  2.0   \n",
       "2   3.0   0.0   0.0  21.0   4.0   5.0   3.0  11.0  0.0  0.0  0.0  4.0   \n",
       "3   3.0   7.0  11.0   3.0  10.0  18.0   3.0  12.0  0.0  0.0  0.0  3.0   \n",
       "4   4.0   4.0  16.0  10.0   5.0  21.0   4.0   8.0  0.0  0.0  0.0  2.0   \n",
       "5  13.0   7.0   7.0   2.0  13.0  17.0  11.0  15.0  0.0  0.0  0.0  6.0   \n",
       "6   3.0   2.0   7.0   7.0   6.0  17.0   3.0   7.0  0.0  0.0  0.0  2.0   \n",
       "7   5.0   4.0   6.0   2.0   7.0   9.0   5.0   9.0  0.0  0.0  0.0  1.0   \n",
       "8   4.0   2.0  11.0  14.0  13.0  32.0   4.0  15.0  0.0  0.0  0.0  0.0   \n",
       "9   4.0   5.0   6.0  14.0  13.0  14.0   4.0  16.0  0.0  0.0  0.0  2.0   \n",
       "\n",
       "   issueDateDT  grade_target_mean  subGrade_target_mean  grade_to_mean_n0  \\\n",
       "0         2587           0.386234              0.380444          1.876011   \n",
       "1         1888           0.304227              0.298190          1.500809   \n",
       "2         3044           0.304227              0.302541          1.500809   \n",
       "3         2679           0.059838              0.065532          0.375202   \n",
       "4         2406           0.224522              0.224686          1.125607   \n",
       "5         3257           0.224522              0.204005          1.125607   \n",
       "6         2983           0.131210              0.128111          0.707941   \n",
       "7         3136           0.059838              0.083522          0.375202   \n",
       "8         3533           0.131210              0.109461          0.750404   \n",
       "9         2526           0.304227              0.279444          1.500809   \n",
       "\n",
       "   grade_to_std_n0  grade_to_mean_n1  grade_to_std_n1  grade_to_mean_n2  \\\n",
       "0         3.992386          1.874620         4.053876          1.942294   \n",
       "1         3.193909          1.502905         3.185919          1.504054   \n",
       "2         3.193909          1.360761         2.998190          1.532981   \n",
       "3         0.798477          0.368239         0.796491          0.383245   \n",
       "4         2.395431          1.113406         2.430896          1.133984   \n",
       "5         2.395431          1.085997         2.408741          0.984707   \n",
       "6         1.635584          0.736477         1.592982          0.766491   \n",
       "7         0.798477          0.371135         0.810299          0.376013   \n",
       "8         1.596954          0.736477         1.592982          0.755989   \n",
       "9         3.193909          1.502905         3.185919          1.511979   \n",
       "\n",
       "   grade_to_std_n2  grade_to_mean_n4  grade_to_std_n4  grade_to_mean_n5  \\\n",
       "0         4.023418          1.869160         3.948124          1.897562   \n",
       "1         3.173189          1.567352         3.204484          1.511316   \n",
       "2         3.241462          1.273891         3.071276          1.162371   \n",
       "3         0.810366          0.380622         0.806605          0.384972   \n",
       "4         2.439745          1.121496         2.368874          1.197930   \n",
       "5         2.361605          1.141867         2.419815          1.133487   \n",
       "6         1.620731          0.720818         1.621383          0.755658   \n",
       "7         0.793297          0.373832         0.789625          0.368325   \n",
       "8         1.626497          0.720818         1.621383          0.769944   \n",
       "9         3.252993          1.494754         3.218213          1.473298   \n",
       "\n",
       "   grade_to_std_n5  grade_to_mean_n6  grade_to_std_n6  grade_to_mean_n7  \\\n",
       "0         4.055665          1.865760         4.017884          1.840872   \n",
       "1         3.139166          1.515599         3.098975          1.500817   \n",
       "2         3.176718          1.480241         3.125317          1.472698   \n",
       "3         0.802575          0.368526         0.819126          0.369865   \n",
       "4         2.401168          1.120956         2.388727          1.106851   \n",
       "5         2.354374          1.100101         2.459716          1.119411   \n",
       "6         1.569583          0.757800         1.549487          0.738697   \n",
       "7         0.815212          0.366700         0.819905          0.375204   \n",
       "8         1.605151          0.739618         1.580526          0.746274   \n",
       "9         3.260850          1.479236         3.161051          1.492548   \n",
       "\n",
       "   grade_to_std_n7  grade_to_mean_n8  grade_to_std_n8  grade_to_mean_n9  \\\n",
       "0         4.074681          1.851544         4.040923          1.938318   \n",
       "1         3.139721          1.517874         3.086106          1.504140   \n",
       "2         3.259745          1.406712         3.254085          1.530998   \n",
       "3         0.798404          0.377964         0.799464          0.382750   \n",
       "4         2.450979          1.144817         2.403154          1.133458   \n",
       "5         2.396658          1.136053         2.409156          1.011351   \n",
       "6         1.625010          0.757368         1.606104          0.765499   \n",
       "7         0.784930          0.364666         0.813245          0.376035   \n",
       "8         1.597772          0.788374         1.610142          0.755638   \n",
       "9         3.195544          1.497336         3.234727          1.511277   \n",
       "\n",
       "   grade_to_std_n9  grade_to_mean_n10  grade_to_std_n10  grade_to_mean_n11  \\\n",
       "0         4.024912           1.842210          4.108917           1.852810   \n",
       "1         3.174194           1.484104          3.173687           1.482248   \n",
       "2         3.244609           1.504230          3.089208           1.482248   \n",
       "3         0.811152           0.370128          0.799459           0.370562   \n",
       "4         2.441340           1.104961          2.446307           1.111686   \n",
       "5         2.376224           1.124941          2.384061           1.111686   \n",
       "6         1.622304           0.736884          1.643567           0.741124   \n",
       "7         0.793549           0.368003          0.809138           0.370562   \n",
       "8         1.627560           0.749961          1.589374           0.741124   \n",
       "9         3.255120           1.496655          3.146687           1.482248   \n",
       "\n",
       "   grade_to_std_n11  grade_to_mean_n12  grade_to_std_n12  grade_to_mean_n13  \\\n",
       "0          4.009823           1.852810          4.009823           1.857394   \n",
       "1          3.207858           1.482248          3.207858           1.485915   \n",
       "2          3.207858           1.482248          3.207858           1.485915   \n",
       "3          0.801965           0.370562          0.801965           0.371479   \n",
       "4          2.405894           1.111686          2.405894           1.114436   \n",
       "5          2.405894           1.111686          2.405894           1.114436   \n",
       "6          1.603929           0.741124          1.603929           0.742958   \n",
       "7          0.801965           0.370562          0.801965           0.371479   \n",
       "8          1.603929           0.741124          1.603929           0.742958   \n",
       "9          3.207858           1.482248          3.207858           1.485915   \n",
       "\n",
       "   grade_to_std_n13  grade_to_mean_n14  grade_to_std_n14  \n",
       "0          4.005352           1.856379          3.991791  \n",
       "1          3.204282           1.485103          3.193433  \n",
       "2          3.204282           1.315111          3.146801  \n",
       "3          0.801070           0.344287          0.793451  \n",
       "4          2.403211           1.113827          2.395075  \n",
       "5          2.403211           0.923430          2.361914  \n",
       "6          1.602141           0.742552          1.596716  \n",
       "7          0.801070           0.395135          0.846111  \n",
       "8          1.602141           0.846155          1.753293  \n",
       "9          3.204282           1.485103          3.193433  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fg_data_train[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "798800c1-2125-443d-aace-6de00cd1c72a",
   "metadata": {},
   "outputs": [],
   "source": [
    "fg_data_train.to_csv('fg_train_data.csv', sep=',', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f530e3d-ea35-4dab-92a1-7fde208dae09",
   "metadata": {},
   "source": [
    "### Upload data\n",
    "\n",
    "To upload the dataset to your own s3 bucket:\n",
    "\n",
    "* Fill {YOUR_S3_BUCKET} and {YOUR_S3_PATH} with your preferred values in the following cell.\n",
    "* Uncomment the cell by removing the leading # character.\n",
    "* Execute the cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "31b33cdb-dcc2-4bbd-bc23-25c231579cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !aws s3 cp ./fg_train_data.csv ${MY_S3_BUCKET}/risk/tianchi/"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
